﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace _02添加水电站
{
    public class ExcelManage
    {
        //获得操作的xls的绝对路径
        public string path;
        //记录xls的连接命令
        public string strConn;
        //获取xls的所有表名，去除了$.
        public List<string> TableName;
        //得到某张表的数据
        public DataTable dataTable;
        //数据显示的控件
        public DataGridView dataview;
        
        //获得xls的绝对路径和xls连接命令
        public void GetPath()
        {
            string path = string.Empty;
            OpenFileDialog openDialog = new OpenFileDialog();
            openDialog.Filter = "Excel file|*.xls|ALl file|*.* ";
            openDialog.InitialDirectory = @"C:\Users\taobaobao\Desktop\上课笔记.txt";
            if (openDialog.ShowDialog() == DialogResult.OK)
            {
                path = openDialog.FileName;
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + @path + ";" + "Extended Properties=Excel 8.0;";
            }
        }
        //获得xls的所有表名
        public void GetTableName()
        {
            TableName = new List<string>();
            using (OleDbConnection OConnection = new OleDbConnection(strConn))
            {
                OConnection.Open();
                DataTable DT2 = OConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                for (int i = 0; i < DT2.Rows.Count; i++)
                {
                    TableName.Add(DT2.Rows[i][2].ToString().Replace("$", "").Replace ("'",""));
                }
            }
        }
        //构造函数，初始化path，strconn，TableName
        public ExcelManage()
        {
            GetPath();
            GetTableName();
        }

        //读取excel第i张表，数据放置在datatable,
        public void  ExcelRead(int i)
        {
            dataTable = new DataTable();
            using (OleDbConnection OConnection = new OleDbConnection(strConn))
            {
                OConnection.Open();
                string conmmand = "select * from [" + TableName [i] + "$]";
                OleDbDataAdapter ODataAdapter = new OleDbDataAdapter(conmmand, strConn);
                ODataAdapter.Fill(dataTable );   
            }
            dataview.DataSource = dataTable;
           // dataview.CellClick += new DataGridViewCellEventHandler(Updata);
        }
        //将datagridview控件的数据存到xls文件中
        //稳健操作成功返回true，失败为false
        public bool DatagridViewToExcel(DataGridView dgv)
        {
            /*设置xls的保存路径
             * 默认文件保存为xls
             * 
             * */
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel File|*.xls";
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "导入到excel表格";
            if (saveFileDialog.ShowDialog() != DialogResult.OK)
            {
                MessageBox.Show("文件操作失败");
                return false;
            }
            using (Stream myStream = saveFileDialog.OpenFile())
            {
                using (StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)))
                {
                    //大量的字符串操作使用StringBuild容器，速度快一个数量级
                    //先添加列名
                    StringBuilder strBuild = new StringBuilder();
                    for (int i = 0; i < dgv.ColumnCount; i++)
                    {
                        if (i > 0)
                        {
                            strBuild.Append("\t");
                        }
                        strBuild.Append(dgv.Columns[i].HeaderText);
                    }
                    sw.WriteLine(strBuild.ToString());
                    //添加数据，数据量比较小，不考虑缓存操作。
                    for (int i = 0; i < dgv.Rows.Count - 1; i++)
                    {
                        strBuild.Clear();
                        for (int j = 0; j < dgv.Columns.Count; j++)
                        {
                            if (j > 0)
                            {
                                strBuild.Append("\t");
                            }
                            strBuild.Append(dgv.Rows[i].Cells[j].Value.ToString());
                        }
                        sw.WriteLine(strBuild.ToString());
                    }
                    //xls保存
                    sw.Close();
                    myStream.Close();
                    return true;

                }
            }
        }
        //读取的xls可能没有主键，无法使用OleDbConnection的UpData操作。
        //public void Updata(object sender, DataGridViewCellEventArgs e)
        //{
            
        //    using (OleDbConnection OConnection = new OleDbConnection(strConn))
        //    {
               
        //        OConnection.Open();
        //      OleDbDataAdapter Oda = new OleDbDataAdapter(  "alter table [" + TableName[2] + "$]"+" add primary key("+"水位"+")",strConn);
        //      OleDbCommandBuilder cmdBuild = new OleDbCommandBuilder(Oda);
        //      Oda.Update(dataTable);
        //      OleDbCommand cmdmand = new OleDbCommand();
        //        cmdmand .CommandText ="select * from [" + TableName[2] +"$]";
        //        cmdmand.Connection = OConnection;
        //        Oda.SelectCommand = cmdmand ;
              
        //      //  Oda.FillSchema(dataTable, SchemaType.Mapped);
        //     //  dataTable.PrimaryKey = new DataColumn[] { dataTable.Columns[dataTable.Columns[1].ColumnName] };
        //        for (int i = 0; i <dataTable .Columns .Count ; i++)
        //        {
        //            dataTable .Rows [e.RowIndex ][i] = dataview.Rows[e.RowIndex].Cells[i].Value ;
        //        }
               
        //        Oda.Update(dataTable);
               
        //    }
        
        //}
    }
}
